/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.thinkinbunny.data;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.thinkinbunny.json.JSONArray;
import org.thinkinbunny.json.JSONException;
import org.thinkinbunny.json.JSONObject;

/**
 *
 * @author yfxiawei
 */
public class DbHelper {

    Connection dbConnect = null;
    Statement statement = null;
    ResultSet resultSet = null;
    //dbConnect.getConn();

//    public boolean updateMaintable() {
//
//        return false;
//
//    }
    public boolean OpenDb() {
        try {
            dbConnect = new DbConnect().getConn();
            statement = dbConnect.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);

            return false;
        }

        return true;
    }

    public void CLoseDb() {

        if (resultSet != null) {
            try {
                resultSet.close();
                resultSet = null;
            } catch (SQLException ex) {
                Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        if (statement != null) {
            try {
                statement.close();
                statement = null;
            } catch (SQLException ex) {
                Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
            }

        }
        if (dbConnect != null) {
            try {
                dbConnect.close();
                dbConnect = null;
            } catch (SQLException ex) {
                Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
            }

        }

    }

    public boolean updateMain(BillEntiry billEntiry, User user) { // 添加用户

        try {


            statement.executeUpdate("insert into jdFinancial values(null,'" + billEntiry.getBillType() + "','"
                    + billEntiry.getAccountname() + "','" + billEntiry.getStore() + "','" + billEntiry.getProject()
                    + "','" + billEntiry.getDate() + "','" + billEntiry.getTime() + "','" + billEntiry.getBalance() + "','" + user.getUsername() + "')");
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }

        return true;
    }

    public boolean updateAccount(String account, User user) { // 添加用户

        try {


            statement.executeUpdate("insert into accounttable values(null,'" + account + "','" + user.getUsername() + "')");
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }

        return true;
    }

    public boolean updateProject(String project, User user) { // 添加用户

        try {

            //    statement = dbConnect.createStatement();
            statement.executeUpdate("insert into projecttable values(null,'" + project + "','" + user.getUsername() + "')");
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }

        return true;
    }

    public boolean updateType(String project, User user) { // 添加用户

        try {

            //  statement = dbConnect.createStatement();
            statement.executeUpdate("insert into typetable values(null,'" + project + "','" + user.getUsername() + "')");
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }

        return true;
    }

    public JSONArray UpdateMainTOClient(User u) {

        JSONArray reSONArray = new JSONArray();
        try {
            statement = dbConnect.createStatement();
            resultSet = statement.executeQuery("select * from jdFinancial where username='" + u.getUsername() + "'");

            while (resultSet.next()) {
                JSONObject billObject = new JSONObject();
                billObject.put("billType",
                        resultSet.getString("billType"));
                billObject.put("accountname",
                        resultSet.getString("accountname"));
                billObject.put("store",
                        resultSet.getString("store"));
                billObject.put("project",
                        resultSet.getString("project"));
                billObject.put("date",
                        resultSet.getString("date"));
                billObject.put("time",
                        resultSet.getString("time"));
                billObject.put("balance",
                        resultSet.getString("balance"));
                reSONArray.put(billObject);

            }
        } catch (JSONException ex) {
            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
        }



        return reSONArray;

    }

    public JSONArray UpdateAccountTOClient(User user) {

        JSONArray reSONArray = new JSONArray();
        try {
            statement = dbConnect.createStatement();
            resultSet = statement.executeQuery("select * from accounttable where username='" + user.getUsername() + "'");

            while (resultSet.next()) {
                JSONObject billObject = new JSONObject();
                billObject.put("account",
                        resultSet.getString("account"));


                reSONArray.put(billObject);

            }
        } catch (JSONException ex) {
            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
        }



        return reSONArray;

    }

    public JSONArray UpdateProjectTOClient(User user) {

        JSONArray reSONArray = new JSONArray();
        try {
            statement = dbConnect.createStatement();
            resultSet = statement.executeQuery("select * from projecttable where username='" + user.getUsername() + "'");

            while (resultSet.next()) {
                JSONObject billObject = new JSONObject();
                billObject.put("project",
                        resultSet.getString("project"));

                reSONArray.put(billObject);
            }
        } catch (JSONException ex) {
            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
        }



        return reSONArray;

    }

    public boolean CheckUser(User user) {

        //User user=new User(username, username);
        try {
            if (user.getUsername().length() < 5 || user.getPassword().length() < 32) {
                return false;
            }
            //      statement = dbConnect.createStatement();

            resultSet = statement.executeQuery("select * from userInfo where username='" + user.getUsername() + "'and password='" + user.getPassword() + "'");
            System.out.println(user.toString());
            while (resultSet.next()) {
                return true;
            }
        } catch (Exception ex) {

            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }



        return false;

    }

    public JSONArray UpdateTypeTOClient(User user) {

        JSONArray reSONArray = new JSONArray();
        try {
            statement = dbConnect.createStatement();
            resultSet = statement.executeQuery("select * from typetable where username='" + user.getUsername() + "'");

            while (resultSet.next()) {
                JSONObject billObject = new JSONObject();
                billObject.put("type",
                        resultSet.getString("type"));
                reSONArray.put(billObject);

            }
        } catch (JSONException ex) {
            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
        }



        return reSONArray;

    }

    public JSONArray UpdateVersion(String versionString) {

        JSONArray reSONArray = new JSONArray();
        try {
            statement = dbConnect.createStatement();
            resultSet = statement.executeQuery("select * from updateVersion");

            while (resultSet.next()) {
                JSONObject billObject = new JSONObject();
                billObject.put("version",
                        resultSet.getString("version"));
                if (resultSet.getString("version").equals(versionString)) {
                    billObject.put("update",
                            false);
                } else {
                    billObject.put("update",
                            true);
                }
                billObject.put("updatecontent",
                        resultSet.getString("updatecontent"));
                billObject.put("updateUrl",
                        resultSet.getString("updateUrl"));
                reSONArray.put(billObject);

            }
        } catch (JSONException ex) {
            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
        }



        return reSONArray;

    }

    public void updateMainClear(User user) { // 添加用户

        try {

            statement.execute("delete from jdFinancial where username='" + user.getUsername() + "'");
            statement.execute("delete from accounttable where username='" + user.getUsername() + "'");
            statement.execute("delete from projecttable where username='" + user.getUsername() + "'");
            statement.execute("delete from typetable   where username='" + user.getUsername() + "'");


        } catch (Exception e) {
            e.printStackTrace();


        }
    }

    public boolean registerUser(User user) { // 添加用户

        try {

            {
                resultSet = statement.executeQuery("select password from userInfo where username='" + user.getUsername() + "'");
                System.out.println("registerUser>>>" + user.toString());
                while (resultSet.next()) {
                    return false;
                }
            }


            int result=statement.executeUpdate("insert into userinfo values(null,'" + user.getUsername() + "','"
                    + user.getPassword() + "','" + user.getEmail() + "')");
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }

        return true;
    }
    
    	public void updateAndroidversion(AndroidVersion androidVersion) { // 添加用户

		try {
			//con = new ConnDB().getConn();
			//statement = statement.createStatement();
			// "update foods set click_count=click_count"+foodcount+" where
			// food_name='"+foodname+"'"  private String version;
  //  private  String updatecontent;
   // private  String updateUrl;
			statement.executeUpdate("update jdfinancial.updateversion set   version='"+androidVersion.getVersion()+"',updatecontent='"+androidVersion.getUpdatecontent()+"',updateUrl='"+androidVersion.getUpdateUrl()+"'  where _id=1;");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
